package com.mengtian_tech.tju.dataquery.api.service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.github.pagehelper.util.StringUtil;

/**
 * 干部请假
 */
@Service
public class AskLeaveService {
	
	@Autowired
	@Qualifier("jdbcTemplateDataAi")
	JdbcTemplate jdbcTemplateDataAi;
	

	@Autowired
	@Qualifier("jdbcTemplateThimis")
	JdbcTemplate jdbcTemplateThimis;

	
	
	@Autowired
	@Qualifier("jdbcTemplateOA")
	JdbcTemplate jdbcTemplateOA;
	
	/**
	 * 同步数据
	 */
	public synchronized int asyncLeave() {
		//查询条件
		String where = "where workflow_id = 5 ";
		
		//首先获取最近的同步信息
		List<Map<String,Object>> asyncList = this.jdbcTemplateDataAi.queryForList(
				"select * from async_log_ask_leave where sync_count > 0   order by id desc limit 0,1");
		if(asyncList.size() > 0) {
			Map<String,Object> map = asyncList.get(0);
			if(map != null && map.get("form_id_end") != null) {
				where += " and id  >  " + map.get("form_id_end") + " ";
			}
		}
		
		
		Date start = new Date();
		int count = 0;
		String startId = null;
		String endId = null;
		
		List<Map<String,Object>> list =  this.jdbcTemplateOA.
				queryForList("SELECT * from app_manager.oa_workflow_doc_form "+where+" order by id asc limit 0,1000 ");
		
		try {		
			for(Map<String,Object> item:list) {
				if(startId == null) startId = item.get("id").toString();
				endId =   item.get("id").toString();
				boolean ret =  insert(item);
				if(ret) count++;
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			Date end = new Date();
			long seconds = (end.getTime() - start.getTime())/1000;
			
			if(count > 0) {
				//插入数据库记录
				this.jdbcTemplateDataAi.update(
						"insert into async_log_ask_leave(task_begin,task_end,sync_count,form_id_begin,form_id_end,duration) values(?,?,?,?,?,?)",
						start,end,count,startId,endId,seconds);
			}
					
		}
		return count;
		
	}

	private boolean insert(Map<String, Object> item) {
		try {
			
			String collegeId = (String) item.get("draft_college_id");
			Map<String,Object> collegeMap = this.jdbcTemplateThimis.queryForMap("select college from app_manager.dict_college  where college_id = ? ",collegeId);
			String collegeName = (String) collegeMap.get("college");
			
			String postLevel = getPostLevel((String) item.get("author_id"));
			Long typeId = (Long) item.get("LEAVE_TYPE");
			String taskType = getType(typeId);
			
			this.jdbcTemplateDataAi.update("insert into ask_leave_log(user_name,user_id,ask_leave_from,ask_leave_to,post_level,leave_dest,"
					+ "task_type_id,task_type,LEAVE_SCHOOL_FEE,LEAVE_SCHOOL_FEE_TYPE,LEAVE_SCHOOL_FEE_USAGE,LEAVE_SCHOOL_FEE_CARD,"					
					+ "LEAVE_INVITOR_FEE,LEAVE_INVITOR_FEE_USAGE,LEAVE_OTHER_FEE,LEAVE_OTHER_FEE_USAGE,content,leave_days,"					
					+ "done,colege_id,college_name,form_id) "
					+ " values(?,?,?,?,?,?,?,?,?,?"
					+ ",?,?,?,?,?,?,?,?,?,?"
					+ ",?,?) ",
					item.get("author"),item.get("author_id"),item.get("LEAVE_FROM_DATE"),item.get("LEAVE_TO_DATE"),postLevel,item.get("leave_dest"),
					typeId,taskType,item.get("LEAVE_SCHOOL_FEE"),item.get("LEAVE_SCHOOL_FEE_TYPE"),item.get("LEAVE_SCHOOL_FEE_USAGE"),item.get("LEAVE_SCHOOL_FEE_CARD"),
					item.get("LEAVE_INVITOR_FEE"),item.get("LEAVE_INVITOR_FEE_USAGE"),item.get("LEAVE_OTHER_FEE"),item.get("LEAVE_OTHER_FEE_USAGE"),item.get("content"),item.get("leave_days"),
					item.get("done"),collegeId,collegeName,item.get("id"));
			return true;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}		
	}

	private String getType(Long typeId) {
		List<Map<String,Object>> list = this.jdbcTemplateOA.queryForList("select * from app_manager.oa_workflow_leave_type where leave_type = ?",typeId);
		if(list.size() > 0) {
			Map<String,Object> map = list.get(0);
			return (String) map.get("leave_type_desc");
		}
		return null;
	}

	/**
	 * 获取职位级别
	 * @param object
	 * @return 1-校领导；2-正职；3-副职
	 */
	
	private String getPostLevel(String staffId) {
		int cnt = this.jdbcTemplateThimis.queryForObject("select count(1) from app_oa.oa_group_member where group_id = 'G14' and member_id = ? ", Integer.class,staffId);
		if(cnt > 0) return "1";
		
		cnt = this.jdbcTemplateThimis.queryForObject("select count(1) from app_manager.employee where apostlevel_id in ('41','43')  and staff_id = ? ", Integer.class,staffId);
		if(cnt > 0) return "2";
		
		cnt = this.jdbcTemplateThimis.queryForObject("select count(1) from app_manager.employee where apostlevel_id in ('42','44')  and staff_id = ? ", Integer.class,staffId);
		if(cnt > 0) return "3";
		
		return "-1";
	}
	
	/**
	 * 进一步处理数据，分析统计
	 * @return
	 */
	public synchronized int dealData() {
		List<Map<String,Object>> asyncList = this.jdbcTemplateDataAi.queryForList(
				"select * from ask_leave_log where deal_flag is null   limit 0,1000");
		
		for(Map<String,Object> map:asyncList) {
			
			String postLevel  = (String) map.get("post_level");
			Long id = (Long) map.get("id");
			Date from = (Date) map.get("ask_leave_from");
			Date to = (Date) map.get("ask_leave_to");
			
			Calendar c = Calendar.getInstance();
			c.setTime(from);
			
			//首先统计开始日期的报表			
			rptByDate(c.getTime(),postLevel);
			
			while(c.getTime().before(to)) {
				//后面一天统计
				c.add(Calendar.DATE, 1);			
				rptByDate(c.getTime(),postLevel);
			}
			
			//标记已处理
			this.jdbcTemplateDataAi.update("update ask_leave_log set deal_flag = 1 where id = ? ",id);
		}
		
		return asyncList.size();		
	}

	private void rptByDate(Date time, String postLevel) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateOn = sdf.format(time);
		
		//去统计表中获取记录，如果没有插入一条
		List<Map<String,Object>> list = this.jdbcTemplateDataAi.queryForList("select * from ask_leave_rpt_count where date_on = ? ",dateOn);
		if(list.size() == 0) {
			 this.jdbcTemplateDataAi.update("insert into ask_leave_rpt_count(date_on,toal_count,level1_count,level2_count,level3_count) values(?,?,?,?,?)"
					 ,dateOn,0,0,0,0);
		}
		
		//重新获取
		list = this.jdbcTemplateDataAi.queryForList("select * from ask_leave_rpt_count where date_on = ? ",dateOn);
		Map<String,Object> item = list.get(0);
		
		Long id = (Long) item.get("id");
		Integer total = (Integer) item.get("toal_count");
		Integer l1Count = (Integer) item.get("level1_count");
		Integer l2Count = (Integer) item.get("level2_count");
		Integer l3Count = (Integer) item.get("level3_count");
		
		total+=1;
		
		if("1".equals(postLevel))l1Count++;
		if("2".equals(postLevel))l2Count++;
		if("3".equals(postLevel))l3Count++;
		
		//更新
		this.jdbcTemplateDataAi.update("update ask_leave_rpt_count"
				+ " set toal_count=?,level1_count=?,level2_count=?,level3_count=?  where id = ?",total,l1Count,l2Count,l3Count, id);
	}
	
	
	public void asyncState() {
		List<Map<String,Object>> asyncList = this.jdbcTemplateDataAi.queryForList(
				"select * from ask_leave_log where done = 0  order by id desc   limit 0,5000");
		
		for(Map<String,Object> map:asyncList) {
			
			Long id = (Long) map.get("id");
			Long formId = new Long( map.get("form_id").toString());			 
			
			List<Map<String,Object>> list  = this.jdbcTemplateOA.queryForList(" select status_id,done  from app_manager.oa_workflow_doc_form  where id = ?",formId);
			
			if(list.size()>0) {
				Map<String,Object> form = list.get(0);
				//更新状态
				this.jdbcTemplateDataAi.update("update ask_leave_log set form_status = ?,done=?   where id = ? "
						,form.get("status_id"),form.get("done"), id);
			}
		
		}
		
	}
	
	
	public List<Map<String,Object>> getTodayList(String postLevel, String taskType) throws ParseException{
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String begin = sdf.format(new Date());
		String end = sdf.format(new Date());
		
		return getLeaveList(1, null, begin, end, taskType,postLevel,500);
	}
	
	public List<Map<String,Object>> getLeaveList(Integer pageNo,String key,String begin,String end,String taskType,String postLevel,Integer pageSize) throws ParseException  {
		
		
		String sql = "select * from ask_leave_log  where 1=1 ";
		List<Object> params =  new ArrayList<Object>();
		if(StringUtil.isNotEmpty(key)) {
			params.add(key);
			params.add(key);			
			sql += " and  (user_id=? or user_name like ?)";
			
		}
		
		if(StringUtil.isNotEmpty(begin)) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date beginDate = sdf.parse(begin);
			Date endDate = sdf.parse(end);
			
			sql += " and( ";
			
			sql += " (ask_leave_from <= ? and ask_leave_to >= ?) or ";
			sql += " (ask_leave_from >= ? and ask_leave_to <= ?) or";
			sql += " (ask_leave_from <= ? and ask_leave_to >= ?) ";
			
			params.add(beginDate);
			params.add(beginDate);
			params.add(beginDate);
			params.add(endDate);
			params.add(endDate);
			params.add(endDate);
			
			sql += ")";
			
		}
		
		if(StringUtil.isNotEmpty(taskType)) {
			sql += " and task_type_id = ?  ";
			params.add(taskType);
		}
		
		if(StringUtil.isNotEmpty(postLevel)) {
			sql += " and post_level = ? ";
			params.add(postLevel);
		}
		
		 
		//排序和分页
		sql +=  " order by ask_leave_from desc , id desc limit ?,?";
		params.add((pageNo-1)*pageSize);
		params.add(pageSize);
		
	 
		//列表
		List<Map<String,Object>> list = this.jdbcTemplateDataAi.queryForList(
				sql,params.toArray());
		
		
		return list;
		
		
	}
	
	
}
